package com.mms.dao;


import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.mms.entity.Device;
import com.mms.entity.DeviceCount;
import com.mms.util.HibernateUtil;

public class DeviceManagementDao {
	

	public boolean AddDevice(Device device){
				Session session = HibernateUtil.getSession();
			try {
				Transaction transaction = session.beginTransaction();
				session.save(device);
				transaction.commit();
				return true;
	        } catch (Exception e) {
	            e.printStackTrace();
	            return false;
	       }finally{
	    	   session.close();
	       }
	}
	
	/*
	public boolean AddDeviceWithoutNumber(Device device){
		 boolean isSuccess = false;
		 Connection conn = new dbConnection().getconn(); 
			try {
				String sql = "insert into device(d_name) values(?)";
	            PreparedStatement pStatement = conn.prepareStatement(sql);
	            pStatement.setString(1,device.getD_name());
	            int row = pStatement.executeUpdate();
	            if(row > 0){
	            	isSuccess = true;
	            }
	            pStatement.close();
	            conn.close();
	        } catch (SQLException e) {
	            e.printStackTrace();
	       }
			return isSuccess;
	}
	*/

	public boolean DeleteDevice(Device device){
			Session session = HibernateUtil.getSession();
		try{
			Transaction transaction = session.beginTransaction();
			session.delete(device);
			transaction.commit();
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			session.close();
		}
	}
	
public List<Device> getDevice(int Spage) {
       		Session session = HibernateUtil.getSession();
       		List<Object[]> objs;
       		List<Device> devices = new ArrayList<Device>();
        try {
            String sql="select * from device limit ?,6";
            Transaction transaction = session.beginTransaction();
            Query query = session.createSQLQuery(sql);
            query.setInteger(0,Spage*6);
            objs = query.list();
            for (Object[] objects : objs) {
                Device device =new Device();
                device.setDId(Integer.parseInt(objects[0].toString()));
                device.setDName(objects[1].toString());
                System.out.println(objects[0].toString());
                System.out.println(objects[1].toString());
                devices.add(device);
            }
           transaction.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	session.close();
        }
        return devices;
    }
  
  
  
  public int getcount(){
		  Session session = HibernateUtil.getSession();
		  int nums = 0;
		  List<BigInteger> num;
		 try{
			 String sql = "select count(d_id) as counts from device";
			 Transaction transaction = session.beginTransaction();
			 Query query = session.createSQLQuery(sql);
			 num = query.list();
			 nums = Integer.parseInt(num.get(0).toString());
			 transaction.commit();
		 }catch(Exception e){
			 e.printStackTrace();
		 }finally{
			 session.close();
		 }
		 return nums;
	 }

public List<Device> getDevicesByTimeAndNumber(String startTime,String endTime) {
      	Session session = HibernateUtil.getSession();
      	List<Object[]> objs;
		List<Device> devicesList =new ArrayList<Device>();
      try {
          String sql="select * from device where d_id not in ( select d_id from configure natural join meeting where (start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) or (start_time > ? and end_time < ?)  )";
          Transaction transaction = session.beginTransaction();
          Query query = session.createSQLQuery(sql);
          query.setString(0,startTime);
          query.setString(1,startTime);
          query.setString(2,endTime);
          query.setString(3,endTime);
          query.setString(4,startTime);
          query.setString(5,endTime);
          objs = query.list();
          for (Object[] objects : objs) {
              Device device =new Device();
              device.setDId(Integer.parseInt(objects[0].toString()));
              device.setDName(objects[1].toString());
              devicesList.add(device);
          }
          transaction.commit();
      } catch (Exception e) {
          e.printStackTrace();
      }finally{
    	  session.close();
      }
      return devicesList;
  }
  
  public List<DeviceCount> getDevicesCountByTimeAndNumber(String startTime,String endTime) {
	  Session session = HibernateUtil.getSession();
	  List<Object[]> objs;
	  List<DeviceCount> deviceCounts =new ArrayList<DeviceCount>();
      try {
          String sql="select  count(*),d_name from device where d_id not in ( select d_id from configure natural join meeting where (start_time < ? and end_time > ?) or  (start_time < ? and end_time > ?) ) group by d_name";
          Transaction transaction = session.beginTransaction();
          Query query = session.createSQLQuery(sql);
          query.setString(0,startTime);
          query.setString(1,startTime);
          query.setString(2,endTime);
          query.setString(3,endTime);
          objs = query.list();
          for (Object[] objects : objs) {
              DeviceCount dCount=new DeviceCount();
              dCount.setDeviceName(objects[1].toString());
              dCount.setCount(Integer.parseInt(objects[0].toString()));
              deviceCounts.add(dCount);
              
          }
         transaction.commit();
      } catch (Exception e) {
          e.printStackTrace();
      }finally{
    	  session.close();
      }
      return deviceCounts;
  }
}
